package com.itee.street.excel;

import com.itee.core.util.CalendarUtils;
import com.itee.street.entity.Term;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;

import java.io.*;
import java.util.List;

/**
 * Created by gonglei on 2015-07-17.
 */
public class ExportXlsData {

    public static void exportExcel( List<Term> terms,String filePath)  {
// Declare a work sheet
        HSSFWorkbook     workbook = new HSSFWorkbook();
// Generate a form
        HSSFSheet sheet = workbook.createSheet("测试记录");
// Set the table for 15 byte default column width
        sheet.setDefaultColumnWidth(15);
// Create a style
        HSSFCellStyle style = workbook.createCellStyle();
// The style settings
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// Create a font
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);
// Create first row
        HSSFRow row = sheet.createRow(0);
//Create first column cell
        HSSFCell cell1 = row.createCell(0);
        cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell1.setCellValue(new HSSFRichTextString("UID"));
//Create second column cell
        HSSFCell cell2 = row.createCell(1);
        cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell2.setCellValue(new HSSFRichTextString("调光状态"));
//Create thrid column cell
        HSSFCell cell3 = row.createCell(2);
        cell3.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell3.setCellValue(new HSSFRichTextString("电压"));
//Create four column cell
        HSSFCell cell4 = row.createCell(3);
        cell4.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell4.setCellValue("电流");
//Create five column cell
        HSSFCell cell5 = row.createCell(4);
        cell5.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell5.setCellValue(new HSSFRichTextString("功率"));
//Create six column cell
        HSSFCell cell6 = row.createCell(5);
        cell6.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell6.setCellValue("功率因素");
//Create seven column cell
        HSSFCell cell7 = row.createCell(6);
        cell7.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell7.setCellValue(new HSSFRichTextString("温度"));

        row.createCell(7).setCellValue("查询时间");
        row.createCell(8).setCellValue("亮灯时长");
        row.createCell(9).setCellValue("累计电量");
        row.createCell(10).setCellValue("软件版本号");
        row.createCell(11).setCellValue("硬件版本号");
        row.createCell(12).setCellValue("备注");

        for(int i = 0; i < terms.size(); i++){
            HSSFRow datarow = sheet.createRow(i+1);
            Term term = terms.get(i);

            HSSFCell datacell1 = datarow.createCell(0);
            datacell1.setCellType(HSSFCell.CELL_TYPE_STRING);
            datacell1.setCellValue(term.getTermUid());
            datarow.createCell(1).setCellValue(term.getDimming());
            datarow.createCell(2).setCellValue(term.getVoltage());
            datarow.createCell(3).setCellValue(term.getCurrent());
            datarow.createCell(4).setCellValue(term.getPower());
            datarow.createCell(5).setCellValue(term.getPowerFactor());
            datarow.createCell(6).setCellValue(term.getTemperature());
            if (term.getCreateTime()!=null){
                datarow.createCell(7).setCellValue(CalendarUtils.formatDateTime(term.getCreateTime()));
            }
            datarow.createCell(8).setCellValue(term.getLightTime());
            datarow.createCell(9).setCellValue(term.getPowerConsume());
            datarow.createCell(10).setCellValue(term.getSoftwareVersion());
            datarow.createCell(11).setCellValue(term.getHardwareVersion());
            datarow.createCell(12).setCellValue(term.getRemark());
        }
        outputExcel(workbook,filePath);
    }

    public static void outputExcel(HSSFWorkbook workbook, String fileName) {
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(new File(fileName));
            workbook.write(fos);
            fos.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
